﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using WF_QLThuVien.Entities;
using WF_QLThuVien.Entities.Sach;
using WF_QLThuVien.Utilities;
using WF_QLThuVien.Entities.DauSach;
using WF_QLThuVien.DataLayer;
using WF_QLThuVien.DataLayer.DauSach;
using System.Data.OleDb;

namespace WF_QLThuVien.DataLayer.DauSach
{
    class CDAL_DauSach : CConnection
    {
        DataAccessHelper db = new DataAccessHelper();
        CDAL_Sach dalSach = new CDAL_Sach();

        public bool Insert(CDauSach ds)
        {
            try
            {
                string cmd = "INSERT INTO DauSach "
                    + "VALUES ('" + ds.MaDauSach + "',"
                    + "'" + ds.TenDauSach + "',"
                    + "#" + ds.NgayNhap + "#,"
                    + "" + ds.SoLuong + ","
                    + "" + ds.GiaTien + ","
                    + "'" + ds.NgonNgu + "',"
                + "" + ds.NamXB + ","
                + "'" + ds.NhaXB + "',"
                + "'" + ds.TheLoai + "',"
                + "'" + ds.TacGia + "',"
                + "'" + ds.TrangThai + "',"
                + "'" + ds.Isbn + "')";
                db.ExecuteNonQuery(cmd);               
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_DauSach.Insert \n" + ex.ToString());
            }
        }

        public bool Delete(string id)
        {
            try
            {
                string cmd = "DELETE FROM DauSach "
                     + "WHERE DauSachID='" + id + "'";
                db.ExecuteNonQuery(cmd);
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_DauSach.DeleteByDauSachID \n" + ex.ToString());
            }
        }

        public bool Update(CDauSach ds)
        {
            try
            {
                string cmd = "UPDATE DauSach SET "
                + "TenDauSach='" + ds.TenDauSach + "',"
                + "SoLuong=" + ds.SoLuong + ","
                + "GiaTien=" + ds.GiaTien + ","
                + "NgonNgu='" + ds.NgonNgu + "',"
                + "NamXB=" + ds.NamXB + ","
                + "NhaXB='" + ds.NhaXB + "',"
                + "TheLoai='" + ds.TheLoai + "',"
                + "TacGia='" + ds.TacGia + "',"
                + "TrangThai='" + ds.TrangThai + "',"
                + "Isbn='" + ds.Isbn + "' "
                + "WHERE DauSachID='" + ds.MaDauSach + "'";
                db.ExecuteNonQuery(cmd);
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_DauSach.UpdateDauSach \n" + ex.ToString());
            }
        }
       
        private List<CDauSach> GetList(string cmd)
        {
            DataTable dt = db.GetDataTable(cmd);
            List<CDauSach> lst = new List<CDauSach>();
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow r in dt.Rows)
                {
                    CDauSach ds = new CDauSach(r);
                    List<CSach> lstSach = new List<CSach>();
                    lstSach = dalSach.GetListByDauSachID(ds.MaDauSach);
                    ds.DsSach = lstSach;

                    lst.Add(ds);
                }
                return lst;
            }
            return null;
        }

        public List<CDauSach> GetListAll()
        {
            try
            {
                string cmd = "SELECT * FROM DauSach";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lối CDAL_DauSach.GetListAll \n" + ex.ToString());
            }
        }

        public CDauSach GetDauSachByDauSachID(string id)
        {
            try
            {
                string cmd = "SELECT * "
                    + "FROM DauSach "
                    + "WHERE DauSachID='" + id + "'";
                List<CDauSach> lst = GetList(cmd);
                if (lst == null)
                {
                    return null;
                }
                return lst[0];
            }
            catch (Exception ex)
            {
                throw new Exception("Lối CDAL_DauSach.GetListDauSachID \n" + ex.ToString());
            }
        }

        public CDauSach GetDauSachBySachID(string id)
        {
            try
            {
                string cmd = "SELECT ds.DauSachID, ds.TenDauSach, NgayNhap, SoLuong, GiaTien, NgonNgu, NamXB, NhaXB, TheLoai, TacGia, ds.TrangThai, Isbn "
                    + "FROM DauSach ds INNER JOIN Sach s ON ds.DauSachID=s.DauSachID"
                    + " WHERE SachID='" + id + "'";
                List<CDauSach> lst = GetList(cmd);
                if (lst == null)
                {
                    return null;
                }
                return lst[0];
            }
            catch (Exception ex)
            {
                throw new Exception("Lối CDAL_DauSach.GetDauSachBySachID \n" + ex.ToString());
            }
        }

        public List<CDauSach> GetListByTenSach(string name)
        {
            try
            {
                string cmd = "SELECT * FROM DauSach"
                    + " WHERE TenDauSach='" + name + "'";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lối CDAL_DauSach.GetListByTenSach \n" + ex.ToString());
            }
        }

        public List<CDauSach> GetListByNgayNhap(DateTime t)
        {
            try
            {
                string cmd = "SELECT * FROM DauSach"
                    + " WHERE NgayNhap=#" + t + "#";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lối CDAL_DauSach.GetListByNgayNhap \n" + ex.ToString());
            }
        }

        public List<CDauSach> GetListByNgonNgu(string name)
        {
            try
            {
                string cmd = "SELECT * FROM DauSach"
                    + " WHERE NgonNgu='" + name + "'";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lối CDAL_DauSach.GetListByNgonNgu \n" + ex.ToString());
            }
        }

        public List<CDauSach> GetListByNamXB(int year)
        {
            try
            {
                string cmd = "SELECT * FROM DauSach"
                    + " WHERE NamXB=" + year + "";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lối CDAL_DauSach.GetListByNamXB \n" + ex.ToString());
            }
        }

        public List<CDauSach> GetListByNhaXB(string name)
        {
            try
            {
                string cmd = "SELECT * FROM DauSach"
                    + " WHERE NhaXB='" + name + "'";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lối CDAL_DauSach.GetListByNhaXB \n" + ex.ToString());
            }
        }

        public List<CDauSach> GetListByTheLoai(string name)
        {
            try
            {
                string cmd = "SELECT * FROM DauSach"
                    + " WHERE TheLoai='" + name + "'";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lối CDAL_DauSach.GetListByTheLoai \n" + ex.ToString());
            }
        }

        public List<CDauSach> GetListByTacGia(string name)
        {
            try
            {
                string cmd = "SELECT * FROM DauSach"
                    + " WHERE TacGia='" + name + "'";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lối CDAL_DauSach.GetListByTacGia \n" + ex.ToString());
            }
        }

        public List<CDauSach> GetListByTrangThai(string name)
        {
            try
            {
                string cmd = "SELECT * FROM DauSach"
                    + " WHERE TrangThai='" + name + "'";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lối CDAL_DauSach.GetListByTrangThai \n" + ex.ToString());
            }
        }

        public List<CDauSach> GetListByIsbn(string name)
        {
            try
            {
                string cmd = "SELECT * FROM DauSach"
                    + " WHERE Isbn='" + name + "'";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lối CDAL_DauSach.GetListByIsbn \n" + ex.ToString());
            }
        }

        public string GetString(string columnsName, string id)
        {
            try
            {
                string cmd = "SELECT " + columnsName + " FROM DauSach WHERE DauSachID='" + id + "'";
                return db.GetValue(cmd).ToString();
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_DauSach.GetString \n" + ex.ToString());
            }            
        }

        public List<string> GetListString(string columnsName)
        {
            string cmd = "SELECT DISTINCT " + columnsName + " FROM DauSach";
            DataTable dt = db.GetDataTable(cmd);
            List<string> lst = new List<string>();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(r[0].ToString());
            }
            return lst;
        }

        public List<CDauSach> GetListByColumnsName(string columnsName, string value)
        {
            try
            {
                string cmd = "SELECT * FROM DauSach WHERE " + columnsName + " LIKE '%" + value + "%'";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_DauSach.GetListByColumnsName \n" + ex.ToString());
            }
        }

        public List<CDauSach> GetListByListColumnsNames(List<string> columnsNames, List<string> values)
        {
            string dk = "";
            int n = columnsNames.Count();
            for (int i = 0; i < n - 1; i++)
            {
                if (columnsNames[i] == "NamXB")
                {
                    dk += columnsNames[i] + "=" + values[i] + " and ";
                }
                else
                {
                    dk += columnsNames[i] + " LIKE '%" + values[i] + "%' and ";
                }
            }

            if (columnsNames[n-1] == "NamXB")
            {
                dk += columnsNames[n - 1] + "=" + values[n - 1];
            }
            else
            {
                dk += columnsNames[n - 1] + " LIKE '%" + values[n - 1] + "%'";
            }

            string cmd = "SELECT * FROM DauSach WHERE " + dk;
            return GetList(cmd);
        }

        #region: PANDA. Tìm Kiếm
        OleDbCommand cmd = new OleDbCommand();
        public void KTConnect()
        {
            //kiem tra lai ket noi
            try { Connection(); }
            catch (Exception ex) { throw ex; }
        }

        public string TrangThaiDauSach(string dauSachID)
        {
            KTConnect();
            try
            {
                cmd = new OleDbCommand("SELECT TrangThai FROM DauSach WHERE DauSachID='" + dauSachID + "'", dataConnection);
                return (string)cmd.ExecuteScalar();
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Khi Tìm Trạng Thái Đầu Sách - CDAL_DauSach\n" + ex.ToString());
            }
        }
        #endregion
    }
}
